﻿using System;
using System.Data;
using WorkOrder.Model;
using System.Collections;

namespace WorkOrder.Business
{
    public class Business_Table_Work_order
    {
        DbHelper dbhelper = new DbHelper();

        #region 保存
        public bool Insert(Model_Table_Work_order model)
        {
            //create_time不能为空。
            string sqlstr =
                "insert into Table_Work_order (entry_person,group_code,is_back,order_content," +
                "order_id,order_number,order_type_id,product_id,remark,town_id,area_id,access_mode,customer_code,clustering_client) values (" +
                model.EntryPerson + ",'" + model.GroupCode + "',0,'" + model.OrderContent + "','" +
                model.Order_id + "','" +
                model.Order_number + "'," + model.Order_type_id + "," + model.Product_id + ",'" + model.Remark + "'," +
                model.Town_id + "," + model.Area_id + ",'" + model.AccessMode + "','" +
                model.CustomerCode + "',";
            if (!string.IsNullOrEmpty(model.ClusteringClient)) sqlstr += "'" + model.ClusteringClient + "');"; else
                sqlstr += "NULL);";
            if (Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr)) == 0)
                return true;
            return false;
        }
        #endregion

        #region 更新
        public bool Update(Model_Table_Work_order model)
        {
            string sql = "";
            string tmp = "";
            if (!string.IsNullOrEmpty(model.Audit_time)) tmp += ",audit_time='" + model.Audit_time + "'"; else tmp += ",audit_time=NULL";
            if (!string.IsNullOrEmpty(model.Analysis_time)) tmp += ",analysis_time='" + model.Analysis_time + "'"; else tmp += ",analysis_time=NULL";
            if (!string.IsNullOrEmpty(model.Config_time)) tmp += ",config_time='" + model.Config_time + "'"; else tmp += ",config_time=NULL";
            if (!string.IsNullOrEmpty(model.Confirm_time)) tmp += ",confirm_time='" + model.Confirm_time + "'"; else tmp += ",confirm_time=NULL";
            if (!string.IsNullOrEmpty(model.TaskTime)) tmp += ",task_time='" + model.TaskTime + "'"; else tmp += ",task_time=NULL";
            if (!string.IsNullOrEmpty(model.BackReason)) tmp += ",back_reason='" + model.BackReason + "'"; else tmp += ",back_reason=NULL";
            if (!string.IsNullOrEmpty(model.BackReasonComment)) tmp += ",back_comment='" + model.BackReasonComment + "'"; else tmp += ",back_comment=NULL";
            if (!string.IsNullOrEmpty(model.ClusteringClient)) tmp += ",clustering_client='" + model.ClusteringClient + "'"; else tmp += ",clustering_client=NULL";
            if (!string.IsNullOrEmpty(model.CustomerCode)) tmp += ",customer_code='" + model.CustomerCode + "'"; else
                tmp += ",customer_code=NULL";
            tmp += " where order_id='" + model.Order_id + "'"; 
            if (string.IsNullOrEmpty(model.FinishTime))
            {
                sql = "update Table_Work_order set product_id=" + model.Product_id + ",order_type_id=" + model.Order_type_id + ",town_id=" + model.Town_id + ",area_id=" + model.Area_id
                    + ",title='" + model.Title + "',group_code='" + model.GroupCode + "',order_content='" + model.OrderContent + "'"
                    + ",is_back=" + (model.IsBack ? 1 : 0) + ",remark='" + model.Remark + "',access_mode='" + model.AccessMode + "'" + tmp;
            }
            else
            {
                sql = "update Table_Work_order set product_id=" + model.Product_id + ",order_type_id=" + model.Order_type_id + ",town_id=" + model.Town_id + ",area_id=" + model.Area_id
                    + ",title='" + model.Title + "',group_code='" + model.GroupCode + "',order_content='" + model.OrderContent + "'"
                    + ",is_back=" + (model.IsBack ? 1 : 0) + ",remark='" + model.Remark + "',finish_time='" + model.FinishTime + "',access_mode='" + model.AccessMode + "'" + tmp;
            }

            if (dbhelper.ExecuteNoQuery(sql) > 0)
                return true;
            return false;
        }
        #endregion

        #region 检查记录是否被引用
        /// <summary>
        /// 检查记录是否被引用
        /// </summary>
        /// <returns>true：已被引用，false：未被引用</returns>
        public bool hasUsed(string keyValue)
        {
            string sql = "select top 1 order_id from Table_Order_record where order_id='" + keyValue + "'";
            return dbhelper.ExecuteScalar(sql) != null;
        }
        #endregion

        #region 检查工单是否已存在
        public bool IsExist(string orderNumber)
        {
            string sqlstr = "select top 1 order_number from Table_Work_order where order_number='" + orderNumber + "'";
            return dbhelper.ExecuteScalar(sqlstr) != null;
        }
        #endregion

        #region 得到单个数据集
        public Model_Table_Work_order GetData(string keyValue)
        {
            string sql = "select * from Table_Work_order where order_id='" + keyValue + "'";
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sql))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Work_order tmp = new Model_Table_Work_order(rdr["order_id"].ToString(), rdr["order_number"].ToString(),
                       rdr["group_code"].ToString(), Convert.ToBoolean(rdr["is_back"]),
                       rdr["product_id"].ToString(), rdr["order_type_id"].ToString(),
                       rdr["town_id"].ToString(), rdr["title"].ToString(), rdr["order_content"].ToString(),
                       rdr["entry_person"].ToString(), rdr["remark"].ToString(),
                       rdr["area_id"].ToString(), !rdr["checkIn_time"].Equals(DBNull.Value) ? rdr["checkIn_time"].ToString() : "", !rdr["access_mode"].Equals(DBNull.Value) ? rdr["access_mode"].ToString() : "",
                       !rdr["analysis_time"].Equals(DBNull.Value) ? rdr["analysis_time"].ToString() : "",
                      !rdr["audit_time"].Equals(DBNull.Value) ? rdr["audit_time"].ToString() : "",
                      !rdr["config_time"].Equals(DBNull.Value) ? rdr["config_time"].ToString() : "",
                      !rdr["confirm_time"].Equals(DBNull.Value) ? rdr["confirm_time"].ToString() : "",
                      !rdr["task_time"].Equals(DBNull.Value) ? rdr["task_time"].ToString() : "", rdr["customer_code"].ToString());
                    if (!DBNull.Value.Equals(rdr["finish_time"]))
                        tmp.FinishTime = rdr["finish_time"].ToString();
                    else tmp.FinishTime = "";
                    tmp.BackReason = !rdr["back_reason"].Equals(DBNull.Value) ? rdr["back_reason"].ToString() : "";
                    tmp.BackReasonComment = !rdr["back_comment"].Equals(DBNull.Value) ? rdr["back_comment"].ToString() : "";
                    tmp.ClusteringClient = !rdr["clustering_client"].Equals(DBNull.Value) ? rdr["clustering_client"].ToString() : "";
                    tmp.CustomerName = !rdr["customer_name"].Equals(DBNull.Value) ? rdr["customer_name"].ToString() : "";
                    tmp.TaskInitiator = !rdr["task_initiator"].Equals(DBNull.Value) ? rdr["task_initiator"].ToString() : "";
                    tmp.TaskDept = !rdr["task_dept"].Equals(DBNull.Value) ? rdr["task_dept"].ToString() : "";
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                return null;
            }
        }
        #endregion

        #region 根据条件得到数据集
        
        public System.Data.DataTable GetDatas(string sqlstr)
        {
            return dbhelper.Query("workordertable", sqlstr);
        }

        public System.Data.DataTable QueryDatas(string condition)
        {
            string sqlstr = "SELECT A.*,B.remark as name_remark FROM (" +
                "SELECT A.*,B.product_name FROM (" +
                "SELECT A.*,B.order_type FROM(" +
                "SELECT A.*,B.town_name FROM ( " +
                "SELECT A.*,B.area_name FROM ( " +
                "select order_id,order_number,title,access_mode,order_status,entry_person,product_id,order_type_id,"+
                "customer_code,checkIn_time,confirm_time,config_time,back_reason,back_comment,audit_time,analysis_time,order_content,customer_name,task_initiator,task_dept," +
                "task_time,group_code,town_id,area_id,finish_time,case when is_back=0 then '否' else '是' end as is_back from Table_Work_order where " + condition + ") A " +
                "LEFT JOIN Table_Area B ON A.area_id=B.area_id) A " +
                "LEFT JOIN Table_Town B ON A.town_id=B.town_id) A " + 
                "LEFT JOIN Table_Order_type B ON A.order_type_id=B.order_type_id) A " +
                "LEFT JOIN Table_Product B ON A.product_id=B.product_id) A " +
                "LEFT JOIN Table_User B ON A.entry_person=B.user_id order by task_time desc";
            return dbhelper.Query("changeovertable", sqlstr);
        }
        #endregion

        

        #region 删除记录
        public bool Delete(string fieldName, string fieldValue)
        {   
            string sqlstr = "delete Table_Work_order where " + fieldName + "=" + fieldValue;
            if (dbhelper.ExecuteNoQuery(sqlstr) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 返回产品类别资料
        public System.Data.DataSet GetProduct()
        {
            string sqlstr = "select product_id,product_name from Table_Product";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据id返回产品类别
        public Model_Table_Product GetProductByID(string id)
        {
            string sqlstr = "select * from Table_Product where product_id=" + id;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Product tmp = new Model_Table_Product(rdr["product_id"].ToString(),rdr["product_name"].ToString(),rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion

        #region 返回工单类型资料
        public System.Data.DataSet GetOrderType()
        {
            string sqlstr = "select order_type_id,order_type from Table_Order_type";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据ID返回工单类型
        public Model_Table_Order_type GetOrderTypeByID(string id)
        {
            string sqlstr = "select * from Table_Order_type where order_type_id=" + id;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Order_type tmp = new Model_Table_Order_type(rdr["order_type_id"].ToString(), rdr["order_type"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion

        #region 返回镇区资料
        public System.Data.DataSet GetTown()
        {
            string sqlstr = "select town_id,town_name from Table_Town";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回区域资料
        public System.Data.DataSet GetArea()
        {
            string sqlstr = "select area_id,area_name from Table_Area";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回子单名称
        public System.Data.DataSet GetProcess()
        {
            string sqlstr = "select process from Table_Process";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回所有用户
        public System.Data.DataSet GetUsers()
        {
            string sqlstr = "select user_id,remark from Table_User";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回区域
        public System.Data.DataSet GetAreas()
        {
            string sqlstr = "select * from Table_Area";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据条件返回区域
        public Model_Table_Area GetArea(string condition)
        {
            if (string.IsNullOrEmpty(condition)) condition = "1=1";
            string sqlstr = "select * from Table_Area where " + condition;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Area tmp = new Model_Table_Area(rdr["area_id"].ToString(), rdr["area_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion

        #region 根据镇区返回区域
        public Model_Table_Area GetAreaByTown(string townid)
        {
            if (!string.IsNullOrEmpty(townid))
            {
                string sqlstr = "select * from Table_Area,Table_Town where Table_Area.area_id=Table_Town.area_id and Table_Town.town_id=" + townid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null && rdr.Read())
                    {
                        Model_Table_Area tmp = new Model_Table_Area(rdr["area_id"].ToString(), rdr["area_name"].ToString(), rdr["remark"].ToString());
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return tmp;
                    }
                    else
                        return new Model_Table_Area("", "", "");
                }
            }
            else return new Model_Table_Area("", "", "");
        }
        #endregion

        #region 根据区域返回镇区
        public ArrayList GetTownByArea(string areaid)
        {
            if (!string.IsNullOrEmpty(areaid))
            {
                ArrayList arr = new ArrayList();
                string sqlstr = "select town_id,town_name from Table_Town where area_id=" + areaid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null)
                    {
                        while (rdr.Read())
                        {
                            object[] tmp = { rdr["town_id"], rdr["town_name"] };
                            arr.Add(tmp);
                        }
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return arr;
                    }
                    else
                        return null;
                }
            }
            else return null;
        }
        #endregion

        #region 根据区域返回镇区ID
        public ArrayList GetTownIdByArea(string areaid)
        {
            if (!string.IsNullOrEmpty(areaid))
            {
                ArrayList arr = new ArrayList();
                string sqlstr = "select town_id from Table_Town where area_id=" + areaid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null)
                    {
                        while (rdr.Read())
                        {
                            arr.Add(rdr["town_id"]);
                        }
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return arr;
                    }
                    else
                        return null;
                }
            }
            else return null;
        }
        #endregion

        #region 根据用户id返回用户姓名
        public Model_Table_User GetUserByID(string userid)
        {
            if (!string.IsNullOrEmpty(userid))
            {
                string sqlstr = "select * from Table_User where user_id=" + userid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    Model_Table_User tmp;
                    if (rdr != null && rdr.Read())
                        tmp = new Model_Table_User(rdr["user_id"].ToString(), rdr["user_name"].ToString(), rdr["password"].ToString(), rdr["remark"].ToString());
                    else
                        tmp = new Model_Table_User("0", "", "", "");
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
            }
            else
                return new Model_Table_User("0", "", "", ""); ;
        }
        #endregion

        #region 查询最近10天创建的工单数量
        public int getTenDayOrderSum()
        {
            string sqlstr = "select COUNT(cast(order_id as varchar(40))) from Table_Work_order where (DATEDIFF(DAY,task_time,GETDATE()))<10";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (obj != null)
                return Convert.ToInt32(obj);
            else
                return 0;
        }
        #endregion 

        #region 查询最近10天每一天的工单数量
        public System.Data.DataSet getTenDayOrder()
        {
            string sqlstr =
                "select substring(convert(varchar(12),task_time,111),6,len(convert(varchar(12),task_time,111))-5) as riqi," +
                "count(cast(order_id as varchar(40))) as shuliang,convert(varchar(12),task_time,111) from Table_Work_order " +
                "where (DATEDIFF(day,task_time,getdate())<=10) and (DATEDIFF(day,task_time,getdate())>=0) " +
                "group by substring(convert(varchar(12),task_time,111),6,len(convert(varchar(12),task_time,111))-5),convert(varchar(12),task_time,111) order by convert(varchar(12),task_time,111) asc";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 查询本周创建的工单数量
        public int getThisWeekOrderSum()
        {
            string sqlstr = "select COUNT(cast(order_id as varchar(40))) from Table_Work_order where DATEDIFF(WK, task_time, GETDATE())=0";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (obj != null)
                return Convert.ToInt32(obj);
            else
                return 0;
        }
        #endregion

        #region 查询本周每一天的工单数量
        public System.Data.DataSet getThisWeekOrder()
        {
            string sqlstr = "select substring(convert(varchar(12),task_time,111),6,len(convert(varchar(12),task_time,111))-5) as riqi," +
                "count(cast(order_id as varchar(40))) as shuliang,convert(varchar(12),task_time,111) from Table_Work_order " +
                "where DATEDIFF(WK,task_time,GETDATE())=0 " +
                "group by substring(convert(varchar(12),task_time,111),6,len(convert(varchar(12),task_time,111))-5),convert(varchar(12),task_time,111) order by convert(varchar(12),task_time,111) asc";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 查询本月创建的工单数量
        public int getThisMonthOrderSum()
        {
            string sqlstr = "select COUNT(cast(order_id as varchar(40))) from Table_Work_order where (DATEDIFF(MM, task_time, GETDATE()) = 0)";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (obj != null)
                return Convert.ToInt32(obj);
            else
                return 0;
        }
        #endregion

        #region 查询本月每一天的工单数量
        public System.Data.DataSet getThisMonthOrder()
        {
            string sqlstr = "select cast(datename(day,task_time) as int) as riqi,count(cast(order_id as varchar(40))) as shuliang from Table_Work_order where DATEDIFF(MM, task_time, GETDATE())=0 group by cast(datename(day,task_time) as int) order by riqi";
            return dbhelper.Query(sqlstr);
        }
        #endregion 

        #region 返回工单问题
        public System.Data.DataSet GetException(string order_id)
        {
            string sqlstr = "select A.exception,A.create_time,B.exception_type,C.remark from Table_Exception as A,Table_Exception_type as B,Table_User as C where order_id='" + order_id + "' and A.exception_type_id=B.exception_type_id and A.creator=C.user_id order by A.create_time DESC";
            return dbhelper.Query(sqlstr);
        }

        public System.Data.DataSet GetExceptionOrder(string condition)
        {
            string sqlstr = "select B.order_id,B.checkIn_time,B.order_number,B.title,B.access_mode,C.area_name,D.town_name,E.remark as name_remark,A.exception_type,A.exception,B.task_time from " +
                "(select A.order_id,B.exception_type,A.exception from Table_Exception A,Table_Exception_type B where A.exception_type_id=B.exception_type_id and" + condition + ") as A," +
                "Table_Work_order as B,Table_Area as C,Table_Town as D,Table_User as E where A.order_id=B.order_id" +
                " and B.area_id=C.area_id and B.town_id=D.town_id and B.entry_person=E.user_id order by B.task_time desc,B.order_number";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据id返回镇区
        public Model_Table_Town GetTownByID(string id)
        {
            string sqlstr = "select * from Table_Town where town_id=" + id;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Town tmp = new Model_Table_Town(rdr["town_id"].ToString(), rdr["area_id"].ToString(), rdr["town_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion

        #region 根据id返回区域
        public Model_Table_Area GetAreaByID(string id)
        {
            string sqlstr = "select * from Table_Area where area_id=" + id;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Area tmp = new Model_Table_Area(rdr["area_id"].ToString(), rdr["area_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                return null;
            }
        }
        #endregion

        #region 每个环节的平均时长
        public System.Data.DataSet GetTimeByStep(string conditions)
        {
            string sqlstr = "select A.step_name as 工单环节,cast(SUM(A.process_time)/COUNT(A.process_time) as decimal(10,2)) as 平均时长 " +
                "from (select B.step_name,A.process_time from Table_Order_record as A, Table_Step as B,Table_Work_order as C where A.step_id=B.step_id " +
                "and A.process_time is not null and A.order_id=C.order_id and" + conditions + ") as A group by A.step_name order by 平均时长 desc";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 每个部门的平均时长
        public System.Data.DataSet GetTimeByDept(string conditions)
        {
            string sqlstr = "select A.dept_name as 部门,cast(SUM(A.process_time)/COUNT(A.process_time) as decimal(10,2)) as 平均时长 " +
                "from (select B.dept_name,A.process_time from Table_Order_record as A, Table_Department as B,Table_Work_order as C where A.dept_id=B.dept_id " +
                "and A.process_time is not null and A.order_id=C.order_id and" + conditions + ") as A group by A.dept_name order by 平均时长 desc";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 工单的平均时长
        public string GetTime(string conditions)
        {
            string sqlstr = "select (select cast(cast(DATEDIFF(\"MINUTE\",create_time,finish_time) as float)/60/24 as decimal(10,2))) as timeLong into #T " +
                "from Table_Work_order where is_finish<>0 and finish_time is not null and" + conditions + ";" +
                "declare @sum decimal(10,2);declare @count int;" +
                "select @sum=SUM(timeLong) from #T;" +
                "select @count=COUNT(timeLong) from #T;" +
                "select cast(@sum/@count as decimal(10,2));" +
                "drop table #T";
            return dbhelper.ExecuteScalar(sqlstr).ToString();
        }
        #endregion

        #region 返回退单原因类型
        public DataSet GetReasonType()
        {
            string sqlstr = "select reason_type from Table_Reason_type where reason_type<>'[空]'";
            return dbhelper.Query(sqlstr);
        }
        #endregion
        
        #region 返回工单状态
        public DataSet GetOrderStatus()
        {
            string sqlstr = "select order_status from Table_Order_status";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        public DataSet GetOrderRecordByOrderID(string order_id)
        {
            string sqlstr = "select order_number,process,current_owner,create_department,operator,request_time,target_sector,response_time,process_time,order_status from Table_Order_record where order_id='" + order_id + "' order by request_time desc";
            return dbhelper.Query(sqlstr);
        }

        #region 返回子单状态
        public DataSet GetOrderRecordStatus()
        {
            string sqlstr = "select order_record_status from Table_Order_Record_Status";
            return dbhelper.Query(sqlstr);
        }

        #endregion 

        #region 返回子单创建人
        public DataSet GetOrderRecordCreator()
        {
            string sqlstr = "select operator from Table_Order_record_Operator";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 
        public DataTable GetOrderRecord(string conditions)
        {
            string sqlstr = "select A.order_number as order_record_number,A.process,A.order_status,A.current_owner," +
                            "A.create_department,A.operator,A.request_time,A.target_sector,A.response_time," +
                            "B.order_id,B.order_number,B.title,C.product_name,D.order_type,E.area_name,F.town_name from " +
                            "Table_Order_record A left join Table_Work_order B on A.order_id=B.order_id " +
                            "left join Table_Product C on B.product_id=C.product_id " +
                            "left join Table_Order_type D on B.order_type_id=D.order_type_id " +
                            "left join Table_Area E on B.area_id=E.area_id " +
                            "left join Table_Town F on B.town_id=F.town_id ";
            if (!string.IsNullOrEmpty(conditions))
            {
                sqlstr += "where" + conditions;
            }

            return dbhelper.Query(sqlstr).Tables[0];
        }
        #endregion

        public int getCurrOnlineOrderSum()
        {
            string sqlstr =
                "select count(cast(order_id as varchar(40))) as shuliang from Table_Work_order where finish_time is null and task_time is not null ";
            int tmp = 0;
            try
            {
                tmp = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr));
            }catch(Exception ex){}
            return tmp;
        }
        public DataSet getCurrOnlineOrderList()
        {
            string sqlstr =
                "select (cast(datename(YEAR,task_time) as varchar(10))+'/'+cast(datename(MONTH,task_time) as varchar(10))) as riqi,count(cast(order_id as varchar(40))) as shuliang from Table_Work_order " +
                "where finish_time is null and task_time is not null " +
                "group by (cast(datename(YEAR,task_time) as varchar(10))+'/'+cast(datename(MONTH,task_time) as varchar(10))) order by riqi";
            return dbhelper.Query(sqlstr);
        }
    }
}
